#!/usr/bin/env python
# coding=utf-8
# __author__ = 'Yunchao Ling'

from xml.etree import ElementTree as ET
import MySQLdb

DATABASE_NAME = 'bdlink'
HOST = '10.10.31.17'
PORT = '3306'
USER_NAME = 'bdlink'
PASSWORD = 'bdlink'
CHAR_SET = 'utf8'

record_tag = {"id": 0, "acc": 1, "name": 2, "description": 3, "authors": 4, "last_modified_date": 5,
              "creation_date": 6, "interproAbstract": 7, "function": 8, "component": 9, "comment": 10, "type": 11,
              "process": 12, "go_id": 13, "interpro_id": 14}

conn = MySQLdb.connect(host=HOST, user=USER_NAME, passwd=PASSWORD, db=DATABASE_NAME, charset=CHAR_SET)

pfam_tree = ET.parse("D:/pfam/PfamFamily.xml")

entries = pfam_tree.getroot().find("entries").findall("entry")
for entry in entries:
    record = ["", "", "", "", "", "", "", "", "", "", "", "", "", "", ""]

    for tag in entry.iter():
        if tag.tag == "entry":
            record[record_tag["id"]] = tag.attrib["id"]
            record[record_tag["acc"]] = tag.attrib["acc"]
        elif tag.tag == "name":
            record[record_tag["name"]] = tag.text
        elif tag.tag == "description":
            record[record_tag["description"]] = tag.text
        elif tag.tag == "authors":
            record[record_tag["authors"]] = tag.text
        elif tag.tag == "date":
            if tag.attrib["type"] == "last_modified":
                record[record_tag["last_modified_date"]] = tag.attrib["value"]
            elif tag.attrib["type"] == "creation":
                record[record_tag["creation_date"]] = tag.attrib["value"]
        elif tag.tag == "field":
            record[record_tag[tag.attrib["name"]]] = tag.text
        elif tag.tag == "ref":
            if tag.attrib["dbname"] == "interpro":
                record[record_tag["interpro_id"]] = tag.attrib["dbkey"]
            elif tag.attrib["dbname"] == "go":
                record[record_tag["go_id"]] = tag.attrib["dbkey"]

    cursor = conn.cursor()
    sql = "insert into PfamFamily values(0,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    params = (
        record[0], record[1], record[2], record[3], record[4], record[5], record[6],
        record[7], record[8], record[9], record[10], record[11], record[12], record[13],
        record[14])
    result = cursor.execute(sql, params)
    conn.commit()
    cursor.close()

conn.close()